#!/usr/bin/env python
# coding=utf-8
"""
@Author  : youjia - 卞志伟
@file    : member_specify.py
@contact : bianzhiwei@iyoujia.com
@time    : 2019-06-28 16:48 
@Desc    : 会员 各项费用及改派订单
@Software: PyCharm
"""
import os
import sys

# 当前文件的路径
pwd, filename = os.path.split(os.path.abspath(__file__))
# 当前文件的父路径
father_path = os.path.abspath(os.path.dirname(pwd) + os.path.sep + ".")
# 当前文件的前两级目录
grader_father = os.path.abspath(os.path.dirname(pwd) + os.path.sep + "..")
sys.path.append(pwd)
sys.path.append(father_path)
sys.path.append(grader_father)

import pandas as pd
from report_system.utils import db_util
from report_system.utils import date_util
from report_system.utils import excel_util
from report_system.utils.log_util import log


def recharge(st, et):
    """
    充值
    :param st:
    :param et:
    :return:
    """
    sql = """SELECT CONCAT(p.user_id,'') 用户id,u.realname 姓名,  

    CASE p.pay_state WHEN 0 THEN '未支付'
    WHEN 1 THEN '已支付'
    ELSE '' END 支付状态,
    
    p.pay_amount/100 支付金额,
    p.gift_amount/100 赠送金额,concat(p.pay_activity_id,'') 用户充值活动ID,a.remark 活动说明,
    CONCAT(p.inviter_user_id,'') 邀请者的用户ID,
    u1.realname 邀请者,p.create_time 创建时间
    
    FROM youjia.user_account_pay_record p
    LEFT JOIN youjia.users u ON p.user_id=u.id
    LEFT JOIN youjia.users u1 ON u1.id=p.inviter_user_id
    LEFT JOIN youjia.user_account_pay_activity a ON p.pay_activity_id=a.id
    WHERE p.user_id IS NOT NULL
    AND p.pay_state=1
    AND DATE(p.create_time) BETWEEN '{st}'  AND '{et}';""".format(st=st, et=et)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def cost(st, et):
    """
    消费
    :param st:
    :param et:
    :return:
    """
    sql = """SELECT CONCAT(r.user_id,'') 用户id,u.realname 姓名,CONCAT(r.order_id,'') 订单id,r.cost_amount/100 消费金额 ,
        CASE r.cost_type WHEN 10 THEN '订单返现余额'
        WHEN 20 THEN '储值余额'
        WHEN 30 THEN '赠金余额'
        WHEN 40 THEN '第三方支付'
        ELSE '' END 消费金额类型,
        r.remark 备注,r.create_time 创建时间,
        (
                                    CASE
                                      b.pay_state
                                      WHEN '0'
                                      THEN '待支付'
                                      WHEN '1'
                                      THEN '已支付'
                                      ELSE ''
                                    END
                                  ) 支付状态,
                    (
                                    CASE
                                      b.order_type
                                      WHEN '0'
                                      THEN '分销'
                                      WHEN '1'
                                      THEN '自建'
                                            WHEN '2'
                                      THEN '有家'
                                      ELSE ''
                                    END
                                  ) 订单类型,

                                 CASE b.cancel_status 
                                                WHEN 0  THEN '未取消'
                                        WHEN 1  THEN '房客取消（支付超时）'
                                        WHEN 2 THEN '房客取消（未付款）'
                                        WHEN 3 THEN '房客取消（全额退款）'
                                        WHEN 4 THEN '房客取消（部分扣款）'
                                        WHEN 5 THEN '房客取消（全额扣款）'
                                        WHEN 6 THEN '管家拒绝（订单取消）'
                                        WHEN 7 THEN '管家拒绝（全额退款）'
                                        WHEN 8 THEN '管家拒绝（确认超时）'
                                        WHEN 9 THEN '无房了'
                                        WHEN 10 THEN '客服介入'
                                        else
                                        ''
                                        end 取消状态,r.create_time 创建时间


        FROM youjia.user_account_cost_record r
        LEFT JOIN youjia.orders b ON r.order_id=b.id
        LEFT JOIN youjia.users u ON r.user_id=u.id
        WHERE r.user_id is NOT NULL
        AND r.order_id IS NOT NULL
        AND date(r.create_time) BETWEEN '{st}' AND  '{et}'
        AND b.pay_state=1
        AND b.confirm_status=1
        AND b.order_channel=4
        AND b.city_id<>376
        AND b.brush_status=0""".format(st=st, et=et)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def refund(st, et):
    """
    退款
    :param st:
    :param et:
    :return:
    """
    sql = """
    SELECT
        CONCAT(r.user_id,'') 用户id,CONCAT(r.order_id,'') 订单id,
        r.refund_amount/100 退款金额,
        CASE r.refund_type WHEN 10 THEN '订单返现余额'
        WHEN 20 THEN '储值余额'
        WHEN 30 THEN '赠金余额'
        WHEN 40 THEN '第三方支付'
        ELSE '' END 消费金额类型,r.remark 备注 ,r.create_time  创建时间,

        (
                                    CASE
                                      b.pay_state
                                      WHEN '0'
                                      THEN '待支付'
                                      WHEN '1'
                                      THEN '已支付'
                                      ELSE ''
                                    END
                                  ) 支付状态,
                    (
                                    CASE
                                      b.order_type
                                      WHEN '0'
                                      THEN '分销'
                                      WHEN '1'
                                      THEN '自建'
                                            WHEN '2'
                                      THEN '有家'
                                      ELSE ''
                                    END
                                  ) 订单类型,

                                 CASE b.cancel_status 
                                                WHEN 0  THEN '未取消'
                                        WHEN 1  THEN '房客取消（支付超时）'
                                        WHEN 2 THEN '房客取消（未付款）'
                                        WHEN 3 THEN '房客取消（全额退款）'
                                        WHEN 4 THEN '房客取消（部分扣款）'
                                        WHEN 5 THEN '房客取消（全额扣款）'
                                        WHEN 6 THEN '管家拒绝（订单取消）'
                                        WHEN 7 THEN '管家拒绝（全额退款）'
                                        WHEN 8 THEN '管家拒绝（确认超时）'
                                        WHEN 9 THEN '无房了'
                                        WHEN 10 THEN '客服介入'
                                        else
                                        ''
                                        end 取消状态,CONCAT(b.cancel_time,'') 取消时间
        FROM user_account_refund_record r
        LEFT JOIN orders b ON r.order_id=b.id
        WHERE r.user_id IS NOT NULL
        AND r.order_id IS NOT NULL
        AND b.pay_state=1
        AND b.confirm_status=1
        AND b.order_channel=4
        AND b.city_id<>376
        AND b.brush_status=0
        AND date(r.create_time) BETWEEN '{st}' AND  '{et}'
    """.format(st=st, et=et)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def free():
    """
    余额 截止当前日期所有
    :return:
    """
    sql = """
       SELECT CONCAT(t.user_id,'') 用户id,u.realname 姓名,u.mobile 电话,
        t.pay_remain_amount/100 储值支付余额,
        t.gift_remain_amount/100 赠送余额,
        t.back_remain_amount/100 返现余额 ,t.create_time 创建时间,t.modify_time 修改时间
        FROM youjia.user_account t
        LEFT JOIN youjia.users u ON t.user_id=u.id
        WHERE t.user_id IS NOT NULL
        -- AND DATE(t.create_time) BETWEEN '{st}' AND  '{et}'
        ORDER BY t.user_id;
   """
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def cashback(st, et):
    """
    返现
    :param st:
    :param et:
    :return:
    """
    sql = """  
    SELECT concat(r.user_id,'') 用户id,u.realname 姓名,
        CASE r.state WHEN 0 THEN '未返现'
        WHEN 1 THEN '已返现'
        WHEN 2 THEN '无效'
        else '' END 返现状态,
        concat(r.order_id,'') 返现的订单ID,
        CASE r.user_level WHEN  0 THEN  '普通会员'
        WHEN 10 THEN '白银'
        WHEN 20 THEN '黄金'
        WHEN 30 THEN '铂金'
        WHEN 40 THEN '钻石'
        ELSE '' END   当前VIP等级,
        r.back_rate 比例,
        r.predict_back_amount/100 预计返现金额,
        r.actual_back_amount/100 实际返现金额,
        r.create_time 创建时间,
        (
                                    CASE
                                      b.pay_state
                                      WHEN '0'
                                      THEN '待支付'
                                      WHEN '1'
                                      THEN '已支付'
                                      ELSE ''
                                    END
                                  ) 支付状态,
                    (
                                    CASE
                                      b.order_type
                                      WHEN '0'
                                      THEN '分销'
                                      WHEN '1'
                                      THEN '自建'
                                            WHEN '2'
                                      THEN '有家'
                                      ELSE ''
                                    END
                                  ) 订单类型,

                                 CASE b.cancel_status 
                                                WHEN 0  THEN '未取消'
                                        WHEN 1  THEN '房客取消（支付超时）'
                                        WHEN 2 THEN '房客取消（未付款）'
                                        WHEN 3 THEN '房客取消（全额退款）'
                                        WHEN 4 THEN '房客取消（部分扣款）'
                                        WHEN 5 THEN '房客取消（全额扣款）'
                                        WHEN 6 THEN '管家拒绝（订单取消）'
                                        WHEN 7 THEN '管家拒绝（全额退款）'
                                        WHEN 8 THEN '管家拒绝（确认超时）'
                                        WHEN 9 THEN '无房了'
                                        WHEN 10 THEN '客服介入'
                                        else
                                        ''
                                        end 取消状态


        FROM user_account_back_record r
        LEFT JOIN orders b ON r.order_id=b.id
        LEFT JOIN users u ON r.user_id=u.id
        WHERE r.user_id IS NOT NULL
        AND r.order_id IS NOT NULL
        AND b.pay_state=1
        AND b.confirm_status=1
        AND b.order_channel=4
        AND b.city_id<>376
        AND b.brush_status=0
        AND  DATE(r.create_time) BETWEEN '{st}' AND  '{et}'
    """.format(st=st, et=et)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def change_order(st, et):
    """
    改派订单
    :param st:
    :param et:
    :return:
    """
    sql = """
        SELECT CONCAT(b.id,'') 订单id,CONCAT(b.parent_id,'')   母订单 FROM  youjia.orders b
        WHERE b.brush_status=0
        AND b.special_status=1
        AND b.parent_id <>0
        AND b.city_id<>376

        AND DATE(b.create_time) BETWEEN '{st}' AND  '{et}';

        """.format(st=st, et=et)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def recharge_week(st=None, et=None):
    """
    一周的 充值
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday

    log.info("充值 st={st}".format(st=st))
    log.info("充值 et={et}".format(et=et))
    recharge_df = recharge(st, et)
    return recharge_df


def recharge_month(st=None, et=None):
    """
    一周的 充值
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday

    log.info("充值 st={st}".format(st=st))
    log.info("充值 et={et}".format(et=et))
    recharge_df = recharge(st, et)
    return recharge_df


def cost_week(st=None, et=None):
    """
    一周的 消费
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday

    log.info("消费 st={st}".format(st=st))
    log.info("消费 et={et}".format(et=et))
    return cost(st, et)


def cost_month(st=None, et=None):
    """
    一月的 消费
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday

    log.info("消费 st={st}".format(st=st))
    log.info("消费 et={et}".format(et=et))
    return cost(st, et)


def refund_week(st=None, et=None):
    """
    一周的 退款
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday

    log.info("退款 st={st}".format(st=st))
    log.info("退款 et={et}".format(et=et))
    return refund(st, et)


def refund_month(st=None, et=None):
    """
    一月的 退款
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday

    log.info("退款 st={st}".format(st=st))
    log.info("退款 et={et}".format(et=et))
    return refund(st, et)


def cashback_week(st=None, et=None):
    """
    一周的 返现
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday

    log.info("返现 st={st}".format(st=st))
    log.info("返现 et={et}".format(et=et))
    return cashback(st, et)


def cashback_month(st=None, et=None):
    """
    一月的 返现
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday

    log.info("返现 st={st}".format(st=st))
    log.info("返现 et={et}".format(et=et))
    return cashback(st, et)


def change_order_week(st=None, et=None):
    """
    一周的 改派订单
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday

    log.info("改派订单 st={st}".format(st=st))
    log.info("改派订单 et={et}".format(et=et))
    return change_order(st, et)


def change_order_month(st=None, et=None):
    """
    一月的 改派订单
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday

    log.info("改派订单 st={st}".format(st=st))
    log.info("改派订单 et={et}".format(et=et))
    return change_order(st, et)


if __name__ == '__main__':
    recharge_week()
